#!/usr/local/bin/python
# -*- coding: utf-8 -*-
#################################################
# 模块功能：处理新的支付宝账单数据库结构
##################################################
from datetime import datetime

from sqlalchemy import Column, DateTime, Integer, Text, Float, Boolean, and_
from sqlalchemy.orm import declarative_base

import init
import initAlipayPage
from init import session
from initLogger import logger

Base = declarative_base()

class AlipayBillNewAlchemy(Base):
    '''
    类功能：存储新的支付宝账单的数据库表
    '''

    __tablename__ = "alipay_bill_new"

    #账单基本字段
    id = Column(Integer, primary_key=True, autoincrement=True, comment="id号")
    year = Column(Integer, comment="账单年份")
    month = Column(Integer, comment="账单月份")
    settlement_time=Column(DateTime, comment="实际结算时间")

    #支付宝账单原始数据字段
    trade_time_first = Column(DateTime, comment="交易时间")
    trade_type_first = Column(Text, comment="交易分类")
    trade_object_first = Column(Text, comment="交易对方")
    trade_account_first = Column(Text, comment="对方账号")
    commodity_description_first = Column(Text, comment="商品说明")

    income_expenses_first = Column(Text, comment="收&支")
    value_first = Column(Float, comment="金额")
    receipt_pay_type_first = Column(Text, comment="收/付款方式")
    trade_status_first = Column(Text, comment="交易状态")
    trade_order_no_first = Column(Text, comment="交易订单号")

    business_order_no_first = Column(Text, comment="商家订单号")
    remark_first = Column(Text, comment="备注")
    trade_order_no_other = Column(Text, comment="交易订单号&交易号")
    business_order_no_other = Column(Text, comment="商家订单号")
    trade_create_time_other = Column(DateTime, comment="交易创建时间")

    trade_payment_time_other = Column(DateTime, comment="付款时间")
    trade_last_modified_time_other = Column(DateTime, comment="最近修改时间")
    trade_source_other = Column(Text, comment="交易来源地")
    type_other = Column(Text, comment="类型")
    trade_object_other = Column(Text, comment="交易对方")
    commodity_name_other = Column(Text, comment="商品名称")
    value_other = Column(Float, comment="金额")
    income_expenses_other = Column(Text, comment="收&支")
    trade_status_other = Column(Text, comment="交易状态")
    value_service_charge_other = Column(Float, comment="服务费（元）")
    value_refund_other = Column(Float, comment="成功退款（元）")
    remark_other = Column(Text, comment="备注")
    fund_status_other = Column(Text, comment="资金状态")

    #账单标签字段
    bill_type = Column(Text, comment="账单类型")
    matched = Column(Boolean, comment="标记是否符合规则")


    ###################################
    # 完善初始化函数
    def __init__(self,
                 tradeTimeFirst="",
                 tradeTypeFirst="",
                 tradeObjectFirst="",
                 tradeAccountFirst="",
                 commodityDescriptionFirst="",
                 incomeExpensesFirst="",
                 valueFirst=0.0,
                 receiptPayTypeFirst="",
                 tradeStatusFirst="",
                 tradeOrderNoFirst="",
                 businessOrderNoFirst="",
                 remarkFirst="",
                 tradeOrderNoOther="",
                 businessOrderNoOther="",
                 tradeCreateTimeOther="",
                 tradePaymentTimeOther="",
                 tradeLastModifiedTimeOther="",
                 tradeSourceOther="",
                 typeOther="",
                 tradeObjectOther="",
                 commodityNameOther="",
                 valueOther="",
                 incomeExpensesOther="",
                 tradeStatusOther="",
                 valueServiceChargeOther="",
                 valueRefundOther="",
                 remarkOther="",
                 fundStatusOther="",
                 billType="",
                 matched="",
                 ):


        if typeOther==initAlipayPage.BILL_TYPE_CREDIT_TRADING:
            self.settlement_time=tradeLastModifiedTimeOther
            if tradeLastModifiedTimeOther!="":
                self.year=tradeLastModifiedTimeOther.year
                self.month=tradeLastModifiedTimeOther.month
        else:
            self.settlement_time = tradeTimeFirst
            if tradeTimeFirst!="":
                self.year=tradeTimeFirst.year
                self.month=tradeTimeFirst.month

        # 支付宝账单原始数据字段
        self.trade_time_first = tradeTimeFirst  # 交易时间
        self.trade_type_first = tradeTypeFirst  # 交易分类
        self.trade_object_first = tradeObjectFirst  # 交易对方
        self.trade_account_first = tradeAccountFirst  # 对方账号

        self.commodity_description_first = commodityDescriptionFirst  # 商品说明
        self.income_expenses_first = incomeExpensesFirst  # 收&支
        self.value_first = valueFirst  # 金额
        self.receipt_pay_type_first = receiptPayTypeFirst  # 收/付款方式
        self.trade_status_first = tradeStatusFirst  # 交易状态

        self.trade_order_no_first = tradeOrderNoFirst  # 交易订单号
        self.business_order_no_first = businessOrderNoFirst  # 商家订单号
        self.remark_first = remarkFirst  # 备注
        self.trade_order_no_other = tradeOrderNoOther  # 交易订单号&交易号
        self.business_order_no_other = businessOrderNoOther  # 商家订单号

        self.trade_create_time_other = tradeCreateTimeOther  # 交易创建时间
        self.trade_payment_time_other = tradePaymentTimeOther  # 付款时间
        self.trade_last_modified_time_other = tradeLastModifiedTimeOther  # 最近修改时间
        self.trade_source_other = tradeSourceOther  # 交易来源地
        self.type_other = typeOther  # 类型

        self.trade_object_other = tradeObjectOther  # 交易对方
        self.commodity_name_other = commodityNameOther  # 商品名称
        self.value_other = valueOther  # 金额
        self.income_expenses_other = incomeExpensesOther  # 收&支
        self.trade_status_other = tradeStatusOther  # 交易状态

        self.value_service_charge_other = valueServiceChargeOther  # 服务费（元）
        self.value_refund_other = valueRefundOther  # 成功退款（元）
        self.remark_other = remarkOther  # 备注
        self.fund_status_other = fundStatusOther  # 资金状态

        # 账单标签字段
        self.bill_type = billType  # 账单类型
        self.matched = matched  # 标记是否符合规则

    '''
    def __init__(self,
                 year=2000,
                 month=1,
                 tradeTimeFirst="",
                 tradeTypeFirst="",
                 tradeObjectFirst="",
                 tradeAccountFirst="",
                 commodityDescriptionFirst="",
                 incomeExpensesFirst="",
                 valueFirst=0.0,
                 receiptPayTypeFirst="",
                 tradeStatusFirst="",
                 tradeOrderNoFirst="",
                 businessOrderNoFirst="",
                 remarkFirst="",
                 tradeOrderNoOther="",
                 businessOrderNoOther="",
                 tradeCreateTimeOther="",
                 tradePaymentTimeOther="",
                 tradeLastModifiedTimeOther="",
                 tradeSourceOther="",
                 typeOther="",
                 tradeObjectOther="",
                 commodityNameOther="",
                 valueOther="",
                 incomeExpensesOther="",
                 tradeStatusOther="",
                 valueServiceChargeOther="",
                 valueRefundOther="",
                 remarkOther="",
                 fundStatusOther="",
                 billType="",
                 matched="",
                ):
        

        self.year = year                                        #账单年份
        self.month = month                                      #账单月份

        # 支付宝账单原始数据字段
        self.trade_time_first = tradeTimeFirst                  #交易时间
        self.trade_type_first = tradeTypeFirst                  #交易分类
        self.trade_object_first = tradeObjectFirst              #交易对方
        self.trade_account_first = tradeAccountFirst            #对方账号

        self.commodity_description_first = commodityDescriptionFirst   #商品说明
        self.income_expenses_first = incomeExpensesFirst        #收&支
        self.value_first = valueFirst                           #金额
        self.receipt_pay_type_first = receiptPayTypeFirst       #收/付款方式
        self.trade_status_first = tradeStatusFirst              #交易状态

        self.trade_order_no_first = tradeOrderNoFirst           #交易订单号
        self.business_order_no_first = businessOrderNoFirst     #商家订单号
        self.remark_first = remarkFirst                         #备注
        self.trade_order_no_other = tradeOrderNoOther           #交易订单号&交易号
        self.business_order_no_other = businessOrderNoOther     #商家订单号

        self.trade_create_time_other = tradeCreateTimeOther     #交易创建时间
        self.trade_payment_time_other = tradePaymentTimeOther   #付款时间
        self.trade_last_modified_time_other = tradeLastModifiedTimeOther    #最近修改时间
        self.trade_source_other = tradeSourceOther              #交易来源地
        self.type_other = typeOther                             #类型

        self.trade_object_other = tradeObjectOther              #交易对方
        self.commodity_name_other = commodityNameOther          #商品名称
        self.value_other = valueOther                           #金额
        self.income_expenses_other = incomeExpensesOther        #收&支
        self.trade_status_other = tradeStatusOther              #交易状态

        self.value_service_charge_other = valueServiceChargeOther   #服务费（元）
        self.value_refund_other = valueRefundOther              #成功退款（元）
        self.remark_other = remarkOther                         #备注
        self.fund_status_other = fundStatusOther                #资金状态

        # 账单标签字段
        self.bill_type = billType                               #账单类型
        self.matched = matched                                  #标记是否符合规则
    '''



    def AddAlipayBillItem(self):
        '''
        函数功能：添加一条数据
        :return:
        '''
        session.add(self)
        session.commit()

    def UpdateAlipayBillItem(self, id):
        '''
        函数功能；更新一条数据
        :return:
        '''


    def SearchAlipayBillByMonthAndType(self, year, month, billType):
        '''
        函数功能：按照条件查询数据
        :param year:
        :param month:
        :return:
        '''

        if billType == initAlipayPage.HUA_BEI_BILL_RADIO_TYPE:
            alipayBillInfo = session.query(AlipayBillNewAlchemy).filter(and_(AlipayBillNewAlchemy.year == year,
                                                                          AlipayBillNewAlchemy.month == month,
                                                                          AlipayBillNewAlchemy.receipt_pay_type_first=="花呗"))\
                                                                .order_by(AlipayBillNewAlchemy.settlement_time)\
                                                                .all()
        if billType == initAlipayPage.CASH_BILL_RADIO_TYPE:
            alipayBillInfo = session.query(AlipayBillNewAlchemy).filter(and_(AlipayBillNewAlchemy.year == year,
                                                                          AlipayBillNewAlchemy.month == month,
                                                                          AlipayBillNewAlchemy.receipt_pay_type_first != "花呗")) \
                                                                .order_by(AlipayBillNewAlchemy.settlement_time) \
                                                                .all()

        if billType == initAlipayPage.LARGE_BILL_RADIO_TYPE:
            alipayBillInfo = session.query(AlipayBillNewAlchemy).filter(and_(AlipayBillNewAlchemy.year == year,
                                                                          AlipayBillNewAlchemy.month == month,
                                                                          AlipayBillNewAlchemy.value_other > initAlipayPage.ALIPAY_BILL_LARGE_VALUE)) \
                                                                .order_by(AlipayBillNewAlchemy.settlement_time) \
                                                                .all()

        if billType == initAlipayPage.UNUSUAL_BILL_RADIO_TYPE:
            alipayBillInfo = session.query(AlipayBillNewAlchemy).filter(and_(AlipayBillNewAlchemy.year == year,
                                                                          AlipayBillNewAlchemy.month == month,
                                                                          AlipayBillNewAlchemy.bill_type == initAlipayPage.UNUSUAL_BILL_TYPE)) \
                                                                .order_by(AlipayBillNewAlchemy.settlement_time) \
                                                                .all()

        if billType == initAlipayPage.INVALID_BILL_RADIO_TYPE:
            alipayBillInfo = session.query(AlipayBillNewAlchemy).filter(and_(AlipayBillNewAlchemy.year == year,
                                                                          AlipayBillNewAlchemy.month == month,
                                                                          AlipayBillNewAlchemy.bill_type == initAlipayPage.INVALID_BILL_TYPE))\
                                                                .order_by(AlipayBillNewAlchemy.settlement_time)\
                                                                .all()



        if billType == initAlipayPage.ALL_BILL_RADIO_TYPE:
            alipayBillInfo = session.query(AlipayBillNewAlchemy).filter(and_(AlipayBillNewAlchemy.year == year,
                                                                              AlipayBillNewAlchemy.month == month)) \
                                                                .order_by(AlipayBillNewAlchemy.settlement_time) \
                                                                .all()


        return alipayBillInfo


    def SearchAlipayBillByMonth(self, year, month):
        '''
        函数功能：按照条件查询数据
        :param year:
        :param month:
        :return:
        '''

        alipayBillInfo = session.query(AlipayBillNewAlchemy).filter(and_(AlipayBillNewAlchemy.year == year,
                                                                        AlipayBillNewAlchemy.month == month)).all()

        return alipayBillInfo


    def SearchAlipayBillByTradeOrderId(self, tradeOrderId):
        '''
        函数功能：查询指定交易订单号的账单信息
        :return:  返回支付宝账单信息
        '''

        alipayBillInfoList = session.query(AlipayBillNewAlchemy).filter(AlipayBillNewAlchemy.trade_order_no_first == tradeOrderId).all()

        if len(alipayBillInfoList)>1:
            logger.warn("SearchAlipayBillByTradeOrderId函数  tradeOrderId信息异常 ")

        return alipayBillInfoList




    def UpdateAlipayBillItemByTradeOrderId(self,
                                              tradeOrderId,
                                              tradeStatusFirst,
                                              tradeStatusOther,
                                              typeOther,
                                              tradePaymentTimeOther,
                                              tradeLastModifiedTimeOther,
                                              ):
        '''
        函数功能：基于tradeOrderId对支付宝账单的数据进行更新
        :param tradeOrderId:
        :return:

        '''

        alipayBillInfoList = session.query(AlipayBillNewAlchemy).filter(
            AlipayBillNewAlchemy.trade_order_no_first == tradeOrderId).all()

        if (len(alipayBillInfoList) > 1 or len(alipayBillInfoList) == 0):
            logger.warn("SearchAlipayBillByTradeOrderId函数  tradeOrderId信息异常 ")

        if typeOther==initAlipayPage.BILL_TYPE_CREDIT_TRADING:#bill type credit trading
            alipayBillInfoList[0].year = tradeLastModifiedTimeOther.year
            alipayBillInfoList[0].month = tradeLastModifiedTimeOther.month
            alipayBillInfoList[0].settlement_time=tradeLastModifiedTimeOther

        alipayBillInfoList[0].trade_status_first = tradeStatusFirst
        alipayBillInfoList[0].trade_status_other = tradeStatusOther
        alipayBillInfoList[0].trade_last_modified_time_other = tradeLastModifiedTimeOther
        alipayBillInfoList[0].trade_payment_time_other = tradePaymentTimeOther

        session.commit()

    def CommitData(self):
        '''
        函数功能：将缓冲中的数据写入数据库
        :return:
        '''
        session.commit()
    def TurnbackBillState(self, year, month):
        '''
        函数功能：对指定月份的账单标记进行回退
        :param year:
        :param month:
        update alipay_bill set bill_type="正常账单",  matched=False where year==2024
        :return:
        '''
        '''
            user_obj = db_session.query(User).filter(User.id == 179074001).update({"name":"XXX"})
            db_session.commit()            
        '''
        session.query(AlipayBillNewAlchemy).filter(and_(AlipayBillNewAlchemy.year==year, AlipayBillNewAlchemy.month==month)) \
                                        .update({AlipayBillNewAlchemy.bill_type:"正常账单", AlipayBillNewAlchemy.matched:False})
        session.commit()



def main():
    alipayBillNewAlchemy = AlipayBillNewAlchemy()
    alipayBillNewAlchemy.SearchAlipayBillByTradeOrderId("2024013122001496501458422808")

    print("good job!")

    '''
    alipayBillNewAlchemy = AlipayBillNewAlchemy(  year=2024,
                                                  month=1,
                                                  tradeTimeFirst=datetime.now(),
                                                  tradeTypeFirst="tradeTypeFirst",
                                                  tradeObjectFirst="tradeObjectFirst",
                                                  tradeAccountFirst="tradeAccountFirst",
                                                  commodityDescriptionFirst="commodityDescriptionFirst",
                                                  incomeExpensesFirst="incomeExpensesFirst",
                                                  valueFirst=0.1,
                                                  receiptPayTypeFirst="receiptPayTypeFirst",
                                                  tradeStatusFirst="tradeStatusFirst",
                                                  tradeOrderNoFirst="tradeOrderNoFirst",
                                                  businessOrderNoFirst="businessOrderNoFirst",
                                                  remarkFirst="remarkFirst",
                                                  tradeOrderNoOther="tradeOrderNoOther",
                                                  businessOrderNoOther="businessOrderNoOther",
                                                  tradeCreateTimeOther=datetime.now(),
                                                  tradePaymentTimeOther=datetime.now(),
                                                  tradeLastModifiedTimeOther=datetime.now(),
                                                  tradeSourceOther="tradeSourceOther",
                                                  typeOther="typeOther",
                                                  tradeObjectOther="tradeObjectOther",
                                                  commodityNameOther="commodityNameOther",
                                                  valueOther=0.1,
                                                  incomeExpensesOther="incomeExpensesOther",
                                                  tradeStatusOther="tradeStatusOther",
                                                  valueServiceChargeOther=0.0,
                                                  valueRefundOther=0.0,
                                                  remarkOther="remarkOther",
                                                  fundStatusOther="fundStatusOther",
                                                  billType="billType",
                                                  matched=True
                                          )


    alipayBillNewAlchemy.AddAlipayBillItem()
    '''




#创建数据库表
AlipayBillNewAlchemy.metadata.create_all(init.engine)


if __name__ == '__main__':
    main()